package com.murong.ai.coder.domain.database.sqlhandler;

import com.murong.ai.coder.entity.TableEntity;
import com.murong.ai.coder.infrastructure.util.MrStringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class PostgresqlHandler {
    /**
     * 支持多条SQL串（建表+注释+索引）
     */
    public static TableEntity handlerCreateTable(String sqlAll) {
        sqlAll = sqlAll.replaceAll("\r\n", "\n");
        // 去除 -- 注释
        sqlAll = sqlAll.replaceAll("--[^\n]*", "");
        TableEntity entity = new TableEntity();
        // 1. 表名
        Pattern tableNamePattern = Pattern.compile("create table\\s+([a-zA-Z0-9_\\.\"]+)", Pattern.CASE_INSENSITIVE);
        Matcher tableNameMatcher = tableNamePattern.matcher(sqlAll);
        if (tableNameMatcher.find()) {
            String tableName = tableNameMatcher.group(1).replace("\"", "");
            if(tableName.contains(".")) {
                entity.setTableNameSnake(tableName.substring(tableName.lastIndexOf(".") + 1));
            } else {
                entity.setTableNameSnake(tableName);
            }
            String tableNameCamel=entity.getTableNameSnake();
            if (tableNameCamel.startsWith("t_")) {
                tableNameCamel=tableNameCamel.substring(2);
            }
            tableNameCamel= MrStringUtils.toCamel(tableNameCamel);
            tableNameCamel=MrStringUtils.toFirstLetter(tableNameCamel);
            entity.setTableNameCamel(tableNameCamel);
        }
        // 2. 字段
        Pattern fieldPattern = Pattern.compile("create table\\s+[a-zA-Z0-9_\\.\"]+\\s*\\((.*?)\\);", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher fieldMatcher = fieldPattern.matcher(sqlAll);
        List<TableEntity.Field> fields = new ArrayList<>();
        if (fieldMatcher.find()) {
            String fieldsBlock = fieldMatcher.group(1);
            String[] lines = fieldsBlock.split(",\n|,\r\n"); // Split by comma and newline
            for (String line : lines) {
                line = line.trim();
                if (line.isEmpty() || line.toLowerCase().startsWith("primary key") || line.toLowerCase().startsWith("unique") || line.toLowerCase().startsWith("constraint")) {
                    continue;
                }
                // 匹配字段名、类型、长度
                Pattern colPattern = Pattern.compile("[\"`]?([a-zA-Z0-9_]+)[\"`]?\\s+([a-zA-Z0-9_]+(?:\\s*\\([^)]*\\))?(\\s*\\[\\])?)(?:\\s+default\\s+[^,]+)?(?:\\s+not\\s+null)?(?:\\s+null)?", Pattern.CASE_INSENSITIVE);
                Matcher colMatcher = colPattern.matcher(line);

                if (colMatcher.find()) {
                    String name = colMatcher.group(1);
                    String dbTypeFull = colMatcher.group(2).trim();
                    String dbType;
                    String length = null;

                    Pattern typePattern = Pattern.compile("([a-zA-Z0-9_]+)(?:\\(([^)]+)\\))?(\\[\\])?");
                    Matcher typeMatcher = typePattern.matcher(dbTypeFull);
                    if (typeMatcher.find()) {
                        dbType = typeMatcher.group(1);
                        if (typeMatcher.group(2) != null) {
                            length = typeMatcher.group(2);
                        }
                        if (typeMatcher.group(3) != null) {
                            dbType += typeMatcher.group(3); // array type
                        }
                    } else {
                        dbType = dbTypeFull;
                    }

                    TableEntity.Field field = new TableEntity.Field();
                    field.setNameSnake(name);
                    field.setNameCamel(MrStringUtils.toCamel(name));
                    field.setDbTyp(dbType); // 存数据库类型
                    field.setType(toJavaType(dbType)); // 存Java类型
                    if (length != null) {
                        String[] lenArr = length.split(",");
                        try { field.setLength(Integer.parseInt(lenArr[0].trim())); } catch (Exception ignore) {}
                        // 可扩展：小数位
                    }
                    // 字段注释（如有） - handled by COMMENT ON later
                    fields.add(field);
                }
            }
        }
        entity.setFields(fields);
        
        // 3. 解析comment on column ... is ...
        Map<String, String> commentMap = new HashMap<>();
        Pattern commentPattern = Pattern.compile("comment on column\\s+([a-zA-Z0-9_\\.\"]+)\\s+is\\s+'([^']*)'", Pattern.CASE_INSENSITIVE);
        Matcher commentMatcher = commentPattern.matcher(sqlAll);
        while (commentMatcher.find()) {
            String fullCol = commentMatcher.group(1);
            String col;
            if (fullCol.contains(".")) {
                col = fullCol.substring(fullCol.lastIndexOf(".") + 1);
            } else {
                col = fullCol;
            }
            col = col.replace("\"", "");
            String comment = commentMatcher.group(2);
            commentMap.put(col.toLowerCase(), comment);
        }
        for (TableEntity.Field f : fields) {
            if (commentMap.containsKey(f.getNameSnake().toLowerCase())) {
                String comment = commentMap.get(f.getNameSnake().toLowerCase());
                String[] parts = comment.split("\\|");
                f.setCommentCn(parts[0]);
                if (parts.length > 1) f.setCommentEn(parts[1]);
            }
        }
        
        // 4. 主键
        Pattern pkPattern = Pattern.compile("primary key\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher pkMatcher = pkPattern.matcher(sqlAll);
        if (pkMatcher.find()) {
            TableEntity.Index pk = new TableEntity.Index();
            String tableName = entity.getTableNameSnake();
            pk.setName("pk_" + tableName);
            pk.setType("PRIMARY");
            String[] pkFields = pkMatcher.group(1).replace("`", "").replace("\"", "").split(",");
            List<String> pkList = new ArrayList<>();
            for (String pkf : pkFields) pkList.add(pkf.trim());
            pk.setFields(pkList);
            entity.setPrimaryKey(pk);
        }

        // 5. 索引
        List<TableEntity.Index> indexes = new ArrayList<>();
        Pattern idxPattern = Pattern.compile(
            "create\\s+(unique\\s+)?index\\s+([a-zA-Z0-9_]+)\\s+on\\s+([a-zA-Z0-9_\\.\"]+)\\s*\\(([^)]+)\\)",
            Pattern.CASE_INSENSITIVE);
        Matcher idxMatcher = idxPattern.matcher(sqlAll);
        while (idxMatcher.find()) {
            TableEntity.Index idx = new TableEntity.Index();
            idx.setName(idxMatcher.group(2));
            idx.setType(idxMatcher.group(1) != null ? "UNIQUE" : "INDEX");
            String[] idxFields = idxMatcher.group(4).replace("`", "").replace("\"", "").split(",");
            List<String> idxList = new ArrayList<>();
            for (String f : idxFields) idxList.add(f.trim());
            idx.setFields(idxList);
            indexes.add(idx);
        }
        entity.setIndexes(indexes);

        // 6. 表注释
        Pattern tableCommentPattern = Pattern.compile("comment on table\\s+([a-zA-Z0-9_\\.\"]+)\\s+is\\s+'([^']*)'", Pattern.CASE_INSENSITIVE);
        Matcher tableCommentMatcher = tableCommentPattern.matcher(sqlAll);
        if (tableCommentMatcher.find()) {
            String comment = tableCommentMatcher.group(2);
            String[] parts = comment.split("\\|");
            entity.setTableCommentCn(parts[0]);
            if (parts.length > 1) entity.setTableCommentEn(parts[1]);
        } else {
            entity.setTableCommentCn(entity.getTableNameCamel());
        }
        return entity;
    }

    private static String toJavaType(String dbType) {
        dbType = dbType.toUpperCase().replace(" ", "");
        if (dbType.endsWith("[]")) {
            return toJavaType(dbType.substring(0, dbType.length() - 2)) + "[]";
        }
        switch (dbType) {
            case "CHARACTERVARYING":
            case "VARCHAR":
            case "CHAR":
            case "TEXT":
            case "BPCHAR":
                return "String";
            case "NUMERIC":
            case "DECIMAL":
                return "java.math.BigDecimal";
            case "INT":
            case "INT4":
            case "INTEGER":
            case "SERIAL":
                return "Integer";
            case "INT2":
            case "SMALLINT":
            case "SMALLSERIAL":
                return "Short";
            case "INT8":
            case "BIGINT":
            case "BIGSERIAL":
                return "Long";
            case "DATE":
                return "java.sql.Date";
            case "TIME":
            case "TIMETZ":
            case "TIMEWITHOUTTIMEZONE":
            case "TIMEWITHTIMEZONE":
                return "java.sql.Time";
            case "TIMESTAMP":
            case "TIMESTAMPTZ":
            case "TIMESTAMPWITHOUTTIMEZONE":
            case "TIMESTAMPWITHTIMEZONE":
                return "java.sql.Timestamp";
            case "FLOAT4":
            case "REAL":
                return "Float";
            case "FLOAT8":
            case "DOUBLEPRECISION":
                return "Double";
            case "BOOL":
            case "BOOLEAN":
                return "Boolean";
            case "BYTEA":
                return "byte[]";
            default:
                return "String";
        }
    }
}
